package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/8/4.
 */
public class RoadLineFaultRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<DTLineFaultDto> getRoadLineFaultCheck4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT RoadLineFaultId,mf.RoadLineId,mf.RoadLineFaultPhoto,mf.RoadLineFaultCreatorId,mf.RoadLineFaultTime,mf.RoadLineFaultReason," +
                "mf.RoadLineCheckerId,mf.RoadLineMoney,mf.RoadLineFaultLevel,mf.RoadLineFaultType,mf.RoadLineMainCheckerId,mf.RoadLineFeedback,\n" +
                "mf.RoadLineMainCheck,mf.RoadLineCheck,mf.RoadLineOrgId,mf.RoadLineMainOrgId,OrgName AS roadLineOrgName,m.RoadLinePhoto \n" +
                "FROM tb_line_fault mf,tb_road_line m,tb_org o\n" +
                "WHERE mf.RoadLineOrgId=o.OrgId AND mf.RoadLineId = m.RoadLineId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.RoadLineCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.RoadLineMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.RoadLineMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.RoadLineMainCheck ,'0,1,-1') , mf.RoadLineFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLineFaultDto.class));
        List<DTLineFaultDto> lampFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lampFaultDtos;
    }

    public Integer getRoadLineFaultCheckCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_line_fault mf,tb_road_line m,tb_org o\n" +
                "WHERE mf.RoadLineOrgId=o.OrgId AND mf.RoadLineId = m.RoadLineId   " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.RoadLineCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " and mf.RoadLineMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTLineFaultDto> getRoadLineFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer maintenanceStatus) throws Exception {
        String sql = "SELECT lf.*,OrgName AS roadLineOrgName,sl.RoadLineStartRoad,sl.RoadLineEndRoad,sl.RoadLineRoadName,u.userName AS roadLineFaultCreatorName," +
                "sl.RoadLinePhoto,sl.RoadLineArea,sl.RoadLineCity,sl.RoadLineProvince FROM tb_road_line sl,tb_line_fault lf,tb_org o,tb_user u\n" +
                "WHERE sl.RoadLineId=lf.RoadLineId AND lf.RoadLineOrgId=o.OrgId AND u.userId=lf.roadLineFaultCreatorId AND lf.RoadLineMainCheck in (1,2) \n" +
                "AND concat(sl.RoadLineStartRoad, sl.RoadLineEndRoad,sl.RoadLineRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.RoadLineCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (maintenanceStatus != 3) {
            sql += " and lf.RoadLineMaintenanceStatus = " + maintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.RoadLineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.RoadLineMainOrgId =" + orgId + "";
        }

        sql += " order by lf.RoadLineMaintenanceStatus ASC,lf.RoadLineMaintenanceTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLineFaultDto.class));
        List<DTLineFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getRoadLineFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer maintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_road_line sl,tb_line_fault lf,tb_org o\n" +
                "WHERE sl.RoadLineId=lf.RoadLineOrgId AND lf.RoadLineOrgId=o.OrgId AND lf.RoadLineMainCheck=1 \n" +
                "AND concat(sl.RoadLineStartRoad, sl.RoadLineEndRoad,sl.RoadLineRoadName) like '%" + dtRequestDto.getSearch() + "%' AND lf.RoadLineCheck=1 ";
        //根据维修人员和系统管理员区分
        if (maintenanceStatus != 3) {
            sql += " and lf.RoadLineMaintenanceStatus = " + maintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.RoadLineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.RoadLineMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<LineFaultDto> getRoadLineFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = " SELECT lf.RoadLineFaultId,lf.RoadLineId,lf.RoadLineFaultPhoto,lf.RoadLineFaultType,lf.RoadLineFaultLevel,lf.RoadLineFaultReason," +
                "lf.RoadLineFaultTime,lf.RoadLineMainCheck,lf.RoadLineCheck,lf.RoadLineMainCheckTime\n" +
                "FROM tb_line_fault lf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (lf.RoadLineCheck =0) OR (lf.RoadLineMainCheck =0 AND lf.RoadLineCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  lf.RoadLineMainCheck =1 AND lf.RoadLineCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( lf.RoadLineCheck =-1) OR (lf.RoadLineMainCheck =-1 AND lf.RoadLineCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.RoadLineCheck=0 AND lf.RoadLineOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.RoadLineCheck=1 AND lf.RoadLineOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.RoadLineCheck=-1 AND lf.RoadLineOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.RoadLineCheck=1 AND lf.RoadLineMainCheck=0 AND lf.RoadLineMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.RoadLineCheck=1 AND lf.RoadLineMainCheck=1 AND lf.RoadLineMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.RoadLineCheck=1 AND lf.RoadLineMainCheck=-1 AND lf.RoadLineMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY lf.RoadLineFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(LineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public Integer getRoadLineReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_line_fault lf JOIN tb_user u ON u.UserId=lf.RoadLineFaultCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=lf.RoadLineOrgId)JOIN tb_road_line rl ON rl.RoadLineId=lf.RoadLineId)\n" +
                "WHERE concat(rl.RoadLineRoadName,rl.RoadLineStartRoad,rl.RoadLineEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and lf.RoadLineOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and lf.RoadLineFaultCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTLineFaultDto> getRoadLineReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT lf.*,u.UserName AS roadLineFaultCreatorName,u2.UserName AS roadLineMaintainerName,u3.UserName " +
                "AS roadLineMainCheckerName, o.OrgName AS roadLineOrgName,rl.RoadLinePhoto from tb_line_fault lf left join tb_user u on" +
                " u.UserId=lf.RoadLineFaultCreatorId left join tb_user u2 on u2.userId = lf.RoadLineMaintainerId " +
                "left join tb_user u3 on u3.userId=lf.RoadLineMainCheckerId left join tb_org o on o.orgId=lf.RoadLineOrgId " +
                "left join tb_road_line rl on rl.RoadLineId=lf.RoadLineId " +
                "WHERE concat(rl.RoadLineRoadName,rl.RoadLineStartRoad,rl.RoadLineEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and lf.RoadLineOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and lf.RoadLineFaultCreatorId=" + userId + "";
        }
        sql += " AND lf.RoadLineCheck IN (0,1,2,-1) \n" +
                " order by find_in_set( lf.RoadLineCheck ,'0,2,1,-1') , lf.RoadLineFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLineFaultDto.class));
        List<DTLineFaultDto> monitorFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return monitorFaultDtos;
    }


    public List<LineFaultDto> getRoadLineFaultReportList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT lf.RoadLineFaultPhoto,lf.RoadLineId,lf.RoadLineFaultType,lf.RoadLineFaultReason,lf.RoadLineFaultId,lf.RoadLineFaultLevel,lf.RoadLineFaultTime,lf.RoadLineCheck,lf.RoadLineMainCheck\n" +
                "FROM tb_road_line sl,tb_line_fault lf\n" +
                "WHERE sl.RoadLineId=lf.RoadLineId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (lf.RoadLineCheck=-1 OR lf.RoadLineMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((lf.RoadLineCheck=1 AND lf.RoadLineMainCheck=0) OR lf.RoadLineCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND lf.RoadLineMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND lf.RoadLineOrgId = " + orgId + " ";
        }
        sql += "ORDER BY RoadLineFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(LineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<LineFaultDto> getRoadLineFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT mf.RoadLineFaultId,mf.RoadLineId,mf.RoadLineFaultPhoto,mf.RoadLineFaultType,mf.RoadLineFaultLevel," +
                "mf.RoadLineFaultReason,mf.RoadLineMaintenanceStatus,mf.RoadLineMainCheckTime,mf.RoadLineMaintenancePhoto," +
                "mf.RoadLineMaintenanceTime\n" +
                "FROM tb_line_fault mf " +
                "WHERE mf.RoadLineMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.RoadLineMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.RoadLineOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.RoadLineMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(LineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public LineFaultDto getRoadLineLatLng(Integer roadLineId) throws Exception {
        String sql = "SELECT GROUP_CONCAT(c.CoordinateLongitude) AS longitude, GROUP_CONCAT(c.CoordinateLatitude) AS latitude\n" +
                " FROM tb_coordinate c\n" +
                " WHERE c.CoordinateTypeId=:roadLineId AND c.CoordinateType=1\n" +
                " ORDER BY c.CoordinateOrder";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(LineFaultDto.class));
        query.setParameter("roadLineId", roadLineId);
        return (LineFaultDto) query.getSingleResult();
    }

}
